from fastapi import APIRouter
import pandas as pd
from pydantic import BaseModel
from routes import sqlite
from util.common import select_file

reci_app = APIRouter()

# 添加收件人
@reci_app.post("/add_recipients")
async def add_recipients():
    excelname = select_file('请选择要生成邮件的excel文件')
    if excelname == '':
        return {
            "code": 204,
            "status": "no",
            "message": "没有选择文件",
            "path": '',
            "data": []
        }
    if sqlite.Execute_one_SQL("DELETE FROM recipients"):
        try:
            df = pd.read_excel(excelname, sheet_name='收件人', usecols=['国家代码', '国家名称', '央库','保税','欧盟','元器件'],
                               dtype={'国家代码': str})
            if df.empty:
                return {
                    "code": 204,
                    "status": "no",
                    "message": "没有数据",
                }
            df.rename(columns={'国家代码': 'country_code', '国家名称': 'country_name', '央库': 'yk','保税':'bs','欧盟':'eu','元器件':'hq2'},
                      inplace=True)
            if sqlite.insert_dataframe(df, 'recipients'):
                return {
                    "code": 200,
                    "status": "ok",
                    "message": "添加成功",
                }
            else:
                return {
                    "code": 204,
                    "status": "no",
                    "message": "添加失败",
                }
        except Exception as e:
            return {
                "code": 204,
                "status": "no",
                "message": str(e),
            }
    else:
        return {
            "code": 204,
            "status": "no",
            "message": "清空表数据失败",
        }


# 查询收件人
@reci_app.get("/query_recipients")
async def query_recipients(country_code: str = "", country_name: str = "", page: int = 1, page_size: int = 10):
    try:
        df = sqlite.query(
            f"SELECT country_code,country_name,yk,bs,eu,hq2 FROM recipients WHERE country_code LIKE '%{country_code}%' AND country_name LIKE '%{country_name}%'")
        if df.empty:
            return {
                "code": 204,
                "status": "no",
                "message": "没有数据",
                "data": []
            }
        df.fillna('', inplace=True)
        total = len(df)
        offset = (page - 1) * page_size
        df = df.iloc[offset: offset + page_size]
        # 将DataFrame转换为字典列表
        dict_list = df.to_dict(orient='records')
        return {
            "code": 200,
            "status": "ok",
            "message": "success",
            "total": total,
            "data": dict_list
        }
    except Exception as e:
        return {
            "code": 204,
            "status": "no",
            "message": str(e),
        }


# 添加或编辑单个收件人
class AddrecipientsType(BaseModel):
    country_code: str
    country_name: str
    yk: str|None = None
    bs: str|None = None
    eu: str|None = None
    hq2: str|None = None


@reci_app.post("/add_oneRecipient")
async def add_oneRecipient(data: AddrecipientsType, isEdit: bool):
    if isEdit:
        sql = f'''
              UPDATE recipients SET country_name='{data.country_name}',yk='{data.yk}',bs='{data.bs}',eu='{data.eu}',hq2='{data.hq2}'
              WHERE country_code='{data.country_code}';
                  '''
    else:
        sql = f'''
               INSERT INTO recipients (country_code,country_name,yk,bs,eu,hq2)
               VALUES ('{data.country_code}','{data.country_name}','{data.yk}','{data.bs}','{data.eu}','{data.hq2}');
           '''
    try:
        if sqlite.Execute_one_SQL(sql):
            return {
                "code": 200,
                "status": "ok",
                "msg": "添加成功",
            }
        else:
            return {
                "code": 204,
                "status": "no",
                "msg": "添加失败",
            }
    except Exception as e:
        return {
            "code": 204,
            "status": "no",
            "msg": str(e),
        }

@reci_app.delete("/del_oneRecipient")
async def del_oneRecipient(country_code: str):
    sql = f'''
          DELETE FROM recipients 
          WHERE country_code='{country_code}';
              '''
    try:
        if sqlite.Execute_one_SQL(sql):
            return {
                "code": 200,
                "status": "ok",
                "msg": "删除成功",
            }
        else:
            return {
                "code": 204,
                "status": "no",
                "msg": "删除失败",
            }
    except Exception as e:
        return {
            "code": 204,
            "status": "no",
            "msg": str(e),
        }
